﻿Imports OMANE.Common.UI

Public Class ViewDataImport

    Sub New()

        ' Cet appel est requis par le concepteur.
        InitializeComponent()

        ' Ajoutez une initialisation quelconque après l'appel InitializeComponent().
       
    End Sub

    Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
        If EXCELRadioButton.IsChecked Then
            LoadExcelDataSource()
        End If
        If SASRadioButton.IsChecked Then
            LoadSasDataSource()
        End If
        'If SQLRadioButton.IsChecked Then
        '    Dim opd = New Microsoft.Win32.OpenFileDialog()
        '    opd.Filter = "Fichiers Base de données Local|*.localdb"
        '    opd.InitialDirectory = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Modules\ProvisionnementModule\Data")

        '    If opd.ShowDialog() Then
        '        FileTextBlock.Text = opd.FileName

        '        Dim cbuilder = New SQLiteConnectionStringBuilder()
        '        cbuilder.DataSource = opd.FileName
        '        cbuilder.FailIfMissing = True

        '        Project.Active.ExternalDataLocalDatabaseConnectionString = cbuilder.ConnectionString
        '        Dim adapter = New SQLiteDataAdapter("SELECT * FROM FINALTABLE", Project.Active.ExternalDataLocalDatabaseConnectionString)
        '        Dim result As New DataTable()
        '        adapter.Fill(result)
        '        ProvisionnementModule._ActiveProject.ExternalData = result
        '        ProvisionnementModule._ActiveProject.NotifyExternalDataChanged()
        '        ProvisionnementModule._ActiveProject.DataModel.NotifySourceColumnsChange()
        '        PreviewDataGrid.ItemsSource = ProvisionnementModule._ActiveProject.ExternalData.DefaultView
        '        If ProvisionnementModule._ActiveProject.DataModel.Count = 0 Then
        '            For Each c As DataColumn In ProvisionnementModule._ActiveProject.ExternalData.Columns
        '                Dim dmp = New DataModelProperty()
        '                dmp.Name = c.ColumnName
        '                dmp.SourceColumn = c
        '                If c.DataType Is GetType(String) Then
        '                    dmp.Type = "Texte"
        '                ElseIf c.DataType Is GetType(Double) Then
        '                    dmp.Type = "Nombre"
        '                End If
        '                ProvisionnementModule._ActiveProject.DataModel.Add(dmp)
        '            Next
        '        End If
        '    End If
        '    Return
        'End If

        'If CSVRadioButton.IsChecked Then


        '    Dim opd = New Microsoft.Win32.OpenFileDialog()
        '    opd.Filter = "Fichiers Texte|*.txt;*.csv"
        '    If opd.ShowDialog() Then
        '        Dim cow = New CsvOptionsWindow()
        '        Dim fl = SimpleCsvReader.ReadFirstLine(opd.FileName)
        '        cow.FirstLine = fl(1)
        '        cow.CsvSeparateurTextBox.Text = fl(0)
        '        cow.CsvSeparateurTextBox_TextChanged(Nothing, Nothing)
        '        cow.ShowDialog()
        '        Dim conf = CType(cow.CsvDatagrid.ItemsSource, IEnumerable(Of CsvOptionsWindow.ColumnOption))
        '        ProvisionnementModule._ActiveProject.ExternalData = SimpleCsvReader.Read( _
        '            opd.FileName, _
        '            cow.CsvSeparateurTextBox.Text, _
        '            If(cow.DecimalComboBox.SelectedIndex = 0, "."c, ","c), _
        '            conf.Select(Function(c) c.Name).ToArray(), _
        '            conf.Select(Function(c) c.Type).ToArray(), _
        '            conf.Select(Function(c) c.Format).ToArray())

        '        'ProvisionnementModule._ActiveProject.ExternalData = SimpleCsvReader.Read(opd.FileName)
        '        ProvisionnementModule._ActiveProject.NotifyExternalDataChanged()
        '        ProvisionnementModule._ActiveProject.DataModel.NotifySourceColumnsChange()
        '        PreviewDataGrid.ItemsSource = ProvisionnementModule._ActiveProject.ExternalData.DefaultView
        '        If ProvisionnementModule._ActiveProject.DataModel.Count = 0 Then
        '            For Each c As DataColumn In ProvisionnementModule._ActiveProject.ExternalData.Columns
        '                Dim dmp = New DataModelProperty()
        '                dmp.Name = c.ColumnName
        '                dmp.SourceColumn = c
        '                If c.DataType Is GetType(String) Then
        '                    dmp.Type = "Texte"
        '                ElseIf c.DataType Is GetType(Double) Then
        '                    dmp.Type = "Nombre"
        '                End If
        '                ProvisionnementModule._ActiveProject.DataModel.Add(dmp)
        '            Next
        '        End If
        '        FileTextBlock.Text = opd.FileName

        '        Dim cbuilder = New SQLiteConnectionStringBuilder()
        '        Dim datafolder = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Modules\ProvisionnementModule\Data")
        '        cbuilder.DataSource = IO.Path.Combine(datafolder, Guid.NewGuid().ToString().Replace("-", "") & ".localdb")
        '        cbuilder.FailIfMissing = False

        '        Dim fun = Function(ColumnOption As CsvOptionsWindow.ColumnOption)
        '                      If ColumnOption.Type = "Texte" Then
        '                          Return ColumnOption.Name.ToUpper() & " TEXT"
        '                      ElseIf ColumnOption.Type = "Nombre" Then
        '                          Return ColumnOption.Name.ToUpper() & " REAL"
        '                      ElseIf ColumnOption.Type = "Date" Then
        '                          Return ColumnOption.Name.ToUpper() & " DATETIME"
        '                      End If

        '                      Return ""
        '                  End Function
        '        Dim con = New SQLiteConnection(cbuilder.ConnectionString)
        '        Dim cmd = New SQLiteCommand()
        '        cmd.CommandText = "DROP TABLE IF EXISTS MAINTABLE;CREATE TABLE MAINTABLE(" & String.Join(",", conf.Select(fun).ToArray()) & ")"

        '        cmd.Connection = con
        '        con.Open()
        '        cmd.ExecuteNonQuery()
        '        con.Close()
        '        con.Dispose()

        '        Project.Active.ExternalDataLocalDatabaseConnectionString = cbuilder.ConnectionString


        '        SimpleCsvReader.ReadToDB( _
        '            opd.FileName, _
        '            cow.CsvSeparateurTextBox.Text, _
        '            If(cow.DecimalComboBox.SelectedIndex = 0, "."c, ","c), _
        '            conf.Select(Function(c) c.Name.ToUpper()).ToArray(), _
        '            conf.Select(Function(c) c.Type).ToArray(), _
        '            conf.Select(Function(c) c.Format).ToArray(), _
        '            "MAINTABLE", _
        '            Project.Active.ExternalDataLocalDatabaseConnectionString)
        '    End If
        'End If

    End Sub

    Private Sub LoadExcelDataSource()
        Dim opd = New Microsoft.Win32.OpenFileDialog()
        opd.Filter = "Fichiers Excel|*.xls;*.xlsx;*.xlsm"
        If opd.ShowDialog() Then
            Dim dsrc = New ExcelDataSource() With {.FileName = opd.FileName, .Name = New IO.FileInfo(opd.FileName).Name.ToUpper().Replace(" ", "_")}
            FileTextBlock.Text = opd.FileName


            Dim oleconstr = New OleDb.OleDbConnectionStringBuilder()
            oleconstr.Provider = "Microsoft.ACE.OLEDB.12.0"
            oleconstr.DataSource = opd.FileName
            oleconstr.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES")

            Dim con = New OleDb.OleDbConnection(oleconstr.ConnectionString)

            con.Open()
            Dim dgrid = New Windows.Controls.DataGrid()
            dgrid.CanUserDeleteRows = False
            dgrid.CanUserAddRows = False
            dgrid.IsReadOnly = True
            dgrid.ItemsSource = con.GetSchema(OleDb.OleDbMetaDataCollectionNames.Tables).DefaultView.ToTable(True, "TABLE_NAME").DefaultView
            ModuleDashboarding._uiservice.ShowMessage(dgrid, Height:=400, Width:=400)
            dsrc.DataSourceType = ExcelDataSource.SourceType.Table
            dsrc.DataSourceName = CType(dgrid.SelectedItem, DataRowView)(0)

            ModuleDashboarding._activeproject.DataSourceList.Add(dsrc)
            con.Close()

            Dim adapter = New OleDb.OleDbDataAdapter("select * from [" & dsrc.DataSourceName & "]", oleconstr.ConnectionString)

            Dim data As New DataTable
            'Dim _idligne_ = data.Columns.Add("_idligne_")
            '_idligne_.DataType = GetType(Double)
            '_idligne_.AllowDBNull = False
            '_idligne_.AutoIncrement = True
            '_idligne_.AutoIncrementSeed = 0
            '_idligne_.AutoIncrementStep = 1
            '_idligne_.Unique = True
            adapter.Fill(data)
            dsrc.Data = data
            dsrc.Columns = (From c As DataColumn In data.Columns Select New DataSourceColumn With {.Name = c.ColumnName, .RuntimeType = c.DataType}).ToArray()
            PreviewDataGrid.ItemsSource = data.DefaultView

            If ModuleDashboarding._activeproject.DataSet.Tables.Contains(dsrc.DataSourceName) Then
                data.TableName = dsrc.DataSourceName & "_1"
            Else
                data.TableName = dsrc.DataSourceName
            End If
            ModuleDashboarding._activeproject.DataSet.Tables.Add(data)

            Dim op = Dispatcher.BeginInvoke(New Action(Of DataSource)(AddressOf CreateCache), Windows.Threading.DispatcherPriority.Background, dsrc)

        End If
    End Sub

    Private Sub PreviewDataGrid_SelectionChanged(sender As Object, e As Windows.Controls.SelectionChangedEventArgs) Handles PreviewDataGrid.SelectionChanged

    End Sub

    Private Sub LoadSasDataSource()
        Dim opd = New Microsoft.Win32.OpenFileDialog()
        opd.Filter = "Fichiers SAS|*.sas7bdat"
        If opd.ShowDialog() Then
            Dim dsrc = New SasDataSource() With {.FileName = opd.FileName, .Name = New IO.FileInfo(opd.FileName).Name.ToUpper().Replace(" ", "_")}
            FileTextBlock.Text = opd.FileName

            Dim oleconstr = New OleDb.OleDbConnectionStringBuilder()
            oleconstr.Provider = "sas.LocalProvider"
            oleconstr.DataSource = New IO.FileInfo(opd.FileName).Directory.FullName
            'oleconstr.Add("SAS Formats", "+_ALL_")
            dsrc.DataSourceName = IO.Path.GetFileNameWithoutExtension(opd.FileName)

            ModuleDashboarding._activeproject.DataSourceList.Add(dsrc)

            Dim con = New OleDb.OleDbConnection(oleconstr.ConnectionString)

            'con.Open()

            ''Dim colinfo = (From r As DataRowView In con.GetSchema(OleDb.OleDbMetaDataCollectionNames.Columns).DefaultView
            ''               Where r("TABLE_NAME") = dsrc.DataSourceName.ToUpper() Select r).ToDictionary(Function(r) CStr(r("COLUMN_NAME")).ToLower(), Function(r) CStr(If(IsDBNull(r("FORMAT_NAME")), "", r("FORMAT_NAME"))))

            'Dim colformat = From r As DataRowView In con.GetSchema(OleDb.OleDbMetaDataCollectionNames.Columns).DefaultView
            '                Where CStr(r("TABLE_NAME")).ToUpper() = dsrc.DataSourceName.ToUpper() And Not IsDBNull(r("FORMAT_NAME"))
            '                Select "" & CStr(r("COLUMN_NAME")).ToLower() & "=" & CStr(If(IsDBNull(r("FORMAT_NAME")), "", r("FORMAT_NAME")))

            'con.Close()

            'oleconstr.Add("SAS Formats", String.Join(",", colformat.ToArray()))
            ' MsgBox(oleconstr.ConnectionString)
            Dim adapter = New OleDb.OleDbDataAdapter(dsrc.DataSourceName, oleconstr.ConnectionString)
            adapter.SelectCommand.CommandType = CommandType.TableDirect
            '   adapter.SelectCommand.Parameters.Add("SAS Formats", OleDb.OleDbType.WChar).Value = "+_ALL"
            '  adapter.SelectCommand
            Dim data As New DataTable
                'adapter.FillSchema(data, SchemaType.Source)

                'For Each c As DataColumn In data.Columns
                '    If c.DataType Is GetType(Double) Then

                '        ' MsgBox(c.ColumnName)

                '        If Not String.IsNullOrEmpty(colinfo(c.ColumnName.ToLower())) Then

                '            '                        MsgBox(c.ColumnName & ":" & colinfo(c.ColumnName.ToLower()))

                '            If colinfo(c.ColumnName.ToLower()).ToLower().StartsWith("ddmmyy") Then

                '                '       MsgBox(c.ColumnName & ":" & colinfo(c.ColumnName.ToLower()))

                '                'c.DateTimeMode =
                '                c.DataType = GetType(Date)

                '            End If

                '        End If

                '    End If
            'Next
            'Dim _idligne_ = data.Columns.Add("_idligne_")
            '_idligne_.DataType = GetType(Double)
            '_idligne_.AllowDBNull = False
            '_idligne_.AutoIncrement = True
            '_idligne_.AutoIncrementSeed = 0
            '_idligne_.AutoIncrementStep = 1
            '_idligne_.Unique = True
            adapter.Fill(data)

            dsrc.Columns = (From c As DataColumn In data.Columns Select New DataSourceColumn With {.Name = c.ColumnName, .RuntimeType = c.DataType}).ToArray()
            dsrc.Data = data
            PreviewDataGrid.ItemsSource = data.DefaultView
            If ModuleDashboarding._activeproject.DataSet.Tables.Contains(dsrc.DataSourceName) Then
                data.TableName = dsrc.DataSourceName & "_1"
            Else
                data.TableName = dsrc.DataSourceName
            End If
            ModuleDashboarding._activeproject.DataSet.Tables.Add(data)
                'Dim con = New OleDb.OleDbConnection(oleconstr.ConnectionString)

                'con.Open()
                'Dim dgrid = New Windows.Controls.DataGrid()
                'dgrid.CanUserDeleteRows = False
                'dgrid.CanUserAddRows = False
                'dgrid.IsReadOnly = True
                'dgrid.ItemsSource = con.GetSchema(OleDb.OleDbMetaDataCollectionNames.Columns).DefaultView
                'ModuleDashboarding._uiservice.ShowMessage(dgrid, Height:=400, Width:=400)

            'con.Close()

            Dim op = Dispatcher.BeginInvoke(New Action(Of DataSource)(AddressOf CreateCache), Windows.Threading.DispatcherPriority.Background, dsrc)

        End If
    End Sub

    Public Sub CreateCache(ByVal dsrc As DataSource)
        Return
        Dim l = Parallel.ForEach(Of DataColumn)(dsrc.Data.Columns.Cast(Of DataColumn).ToArray(), _
                                        Sub(c)
                                            Dim tbl = dsrc.Data.DefaultView.ToTable(True, c.ColumnName)
                                            tbl.TableName = "table_" & c.ColumnName
                                            tbl.Columns(0).ColumnName = "Member"
                                            dsrc.Data.DataSet.Tables.Add(tbl)

                                        End Sub)



    End Sub
End Class

Public Class ViewModelDataImport
    Implements IViewModelTabSection


    Sub New()
        Title = "Import Donnees"

        Icone = StandardIcons.GRID
    End Sub

    Public Property PreviewDataGridItemsSource As IEnumerable

    Public Property EXCELRadioButtonIsChecked As Boolean

    Public Property FileTextBlockText As String

    Public Property Icone As String Implements IViewModelTabSection.Icone

    Public Property Title As String Implements IViewModelTabSection.Title

    Public Property IsClosable As Boolean Implements IViewModelTabSection.IsClosable
End Class